import { Sequelize, QueryTypes, Options as SequelizeOptions, DataTypes, Model } from 'sequelize';
import * as path from 'path';
import * as fs from 'fs';
import * as os from 'os';

// 添加连接池配置类型
interface PoolConfig {
  max?: number;        // 最大连接数
  min?: number;        // 最小连接数
  idle?: number;       // 连接空闲超时（毫秒）
  acquire?: number;    // 连接获取超时（毫秒）
}

// 数据库连接信息接口
interface DatabaseConnectionInfo {
  name: string;
  dialect: string;
  host: string;
  port?: number;
  username?: string;
  password?: string;
  database?: string;
  storage?: string;
  pool?: PoolConfig;
}

// 数据库连接管理器
export class DatabaseManager {
  private connections: Map<string, Sequelize> = new Map();
  private connectionConfigs: Map<string, DatabaseConnectionInfo> = new Map(); // 存储连接配置但不立即连接
  private defaultPoolConfig: PoolConfig = {
    max: 10,          // 默认最大10个连接
    min: 0,          // 默认最小0个连接
    idle: 10000,     // 空闲超时10秒
    acquire: 30000   // 获取超时30秒
  };
  private dataWiseDir: string;
  private configDB: Sequelize | null = null;
  private ConnectionModel: any;
  private configInitialized: boolean = false;

  constructor() {
    // 在用户目录下创建.datawise文件夹
    this.dataWiseDir = path.join(os.homedir(), '.datawise');
    if (!fs.existsSync(this.dataWiseDir)) {
      fs.mkdirSync(this.dataWiseDir, { recursive: true });
    }
  }

  private async initConfigDb() {
    if (this.configInitialized) return;
    
    try {
      const dbPath = path.join(this.dataWiseDir, 'database.db');
      this.configDB = new Sequelize({
        dialect: 'sqlite',
        storage: dbPath,
        logging: false
      });

      // 定义连接信息模型
      this.ConnectionModel = this.configDB.define('ConnectionInfo', {
        name: {
          type: DataTypes.STRING,
          primaryKey: true
        },
        dialect: {
          type: DataTypes.STRING,
          allowNull: false
        },
        host: {
          type: DataTypes.STRING,
          allowNull: true
        },
        port: {
          type: DataTypes.INTEGER,
          allowNull: true
        },
        username: {
          type: DataTypes.STRING,
          allowNull: true
        },
        password: {
          type: DataTypes.STRING,
          allowNull: true
        },
        database: {
          type: DataTypes.STRING,
          allowNull: true
        },
        storage: {
          type: DataTypes.STRING,
          allowNull: true
        },
        pool: {
          type: DataTypes.TEXT, // JSON字符串存储
          allowNull: true,
          get() {
            const rawValue = this.getDataValue('pool');
            return rawValue ? JSON.parse(rawValue) : null;
          },
          set(value: any) {
            this.setDataValue('pool', value ? JSON.stringify(value) : null);
          }
        }
      });

      // 同步数据库结构
      await this.ConnectionModel.sync();
      this.configInitialized = true;
      console.log('配置数据库初始化成功');
    } catch (error: any) {
      console.error('配置数据库初始化失败:', error.message);
      throw error;
    }
  }

  async initialize(dbListStr?: string) {
    try {
      // 初始化配置数据库
      await this.initConfigDb();
      
      // 如果提供了dbListStr，则将其作为初始数据导入
      if (dbListStr) {
        const dbList = JSON.parse(dbListStr);
        if (Array.isArray(dbList)) {
          for (const db of dbList) {
            await this.addDatabaseConnection(db, false); // 不立即连接
          }
        }
      }
      
      // 从配置数据库加载连接信息到内存但不立即连接
      await this.loadConnectionConfigs();
      
      // 不再自动连接数据库
      console.log(`已初始化 ${this.connectionConfigs.size} 个数据库配置，将在需要时进行连接。`);
    } catch (error: any) {
      console.error('初始化数据库配置失败:', error.message);
      throw error;
    }
  }
  
  // 加载配置但不连接
  private async loadConnectionConfigs() {
    try {
      if (!this.configInitialized) {
        await this.initConfigDb();
      }

      const connections = await this.ConnectionModel.findAll();
      for (const conn of connections) {
        const connInfo = conn.toJSON();
        this.connectionConfigs.set(connInfo.name, connInfo);
        console.log(`数据库 '${connInfo.name}' 配置已加载（未连接）`);
      }
      
      return true;
    } catch (error: any) {
      console.error('加载数据库配置失败:', error.message);
      return false;
    }
  }
  
  private async connectToDatabase(connInfo: DatabaseConnectionInfo) {
    try {
      // 避免重复连接
      if (this.connections.has(connInfo.name)) {
        return true;
      }
      
      const dbConfig: any = {
        dialect: connInfo.dialect || 'mysql',
        host: connInfo.host || 'localhost',
        logging: false,
        pool: {
          ...this.defaultPoolConfig,
          ...(connInfo.pool || {})
        }
      };

      if (connInfo.port) dbConfig.port = connInfo.port;
      if (connInfo.username) dbConfig.username = connInfo.username;
      if (connInfo.password) dbConfig.password = connInfo.password;
      if (connInfo.database) dbConfig.database = connInfo.database;
      if (connInfo.storage) dbConfig.storage = connInfo.storage;

      const sequelize = new Sequelize(dbConfig);
      this.connections.set(connInfo.name, sequelize);
      console.log(`数据库 '${connInfo.name}' 连接已创建`);
      return true;
    } catch (error: any) {
      console.error(`连接到数据库 '${connInfo.name}' 失败:`, error.message);
      return false;
    }
  }

  // 添加新的数据库连接
  async addDatabaseConnection(connInfo: DatabaseConnectionInfo, connectNow: boolean = false): Promise<boolean> {
    try {
      if (!connInfo.name) {
        throw new Error('数据库连接必须有名称');
      }
      
      // 确保配置数据库已初始化
      if (!this.configInitialized) {
        await this.initConfigDb();
      }
      
      // 检查是否已存在同名连接
      const existingConn = await this.ConnectionModel.findByPk(connInfo.name);
      if (existingConn) {
        throw new Error(`数据库连接 '${connInfo.name}' 已存在`);
      }
      
      // 保存到配置数据库
      await this.ConnectionModel.create(connInfo);
      
      // 添加到配置映射
      this.connectionConfigs.set(connInfo.name, connInfo);
      console.log(`数据库 '${connInfo.name}' 配置已添加（未连接）`);
      
      // 如果需要立即连接
      if (connectNow) {
        return await this.connectToDatabase(connInfo);
      }
      
      return true;
    } catch (error: any) {
      console.error(`添加数据库连接失败:`, error.message);
      throw error;
    }
  }
  
  // 删除数据库连接
  async deleteDatabaseConnection(name: string): Promise<boolean> {
    try {
      // 确保配置数据库已初始化
      if (!this.configInitialized) {
        await this.initConfigDb();
      }
      
      // 检查连接配置是否存在
      if (!this.connectionConfigs.has(name)) {
        throw new Error(`数据库连接配置 '${name}' 不存在`);
      }
      
      // 如果连接是打开的，先关闭连接
      if (this.connections.has(name)) {
        try {
          const connection = this.connections.get(name)!;
          await connection.close();
          console.log(`数据库 '${name}' 连接已关闭`);
          // 从连接映射中移除
          this.connections.delete(name);
        } catch (error: any) {
          console.error(`关闭数据库连接 '${name}' 错误:`, error.message);
        }
      }
      
      // 从配置映射中移除
      this.connectionConfigs.delete(name);
      
      // 从配置数据库中删除
      const rowsDeleted = await this.ConnectionModel.destroy({
        where: { name }
      });
      
      console.log(`数据库连接 '${name}' 已从配置中删除`);
      return rowsDeleted > 0;
    } catch (error: any) {
      console.error(`删除数据库连接失败:`, error.message);
      throw error;
    }
  }
  
  // 更新数据库连接
  async updateDatabaseConnection(name: string, connInfo: Partial<DatabaseConnectionInfo>, reconnect: boolean = false): Promise<boolean> {
    try {
      // 确保配置数据库已初始化
      if (!this.configInitialized) {
        await this.initConfigDb();
      }
      
      // 检查连接是否存在
      const existingConn = await this.ConnectionModel.findByPk(name);
      if (!existingConn) {
        throw new Error(`数据库连接 '${name}' 不存在`);
      }
      
      // 获取现有连接信息
      const existingConnInfo = existingConn.toJSON();
      
      // 只更新提供的字段，保留其他字段不变
      const updatedConnInfo: DatabaseConnectionInfo = {
        name: name,  // 名称不能修改
        dialect: connInfo.dialect || existingConnInfo.dialect,
        host: connInfo.host || existingConnInfo.host,
        port: connInfo.port !== undefined ? connInfo.port : existingConnInfo.port,
        username: connInfo.username !== undefined ? connInfo.username : existingConnInfo.username,
        password: connInfo.password !== undefined ? connInfo.password : existingConnInfo.password,
        database: connInfo.database !== undefined ? connInfo.database : existingConnInfo.database,
        storage: connInfo.storage !== undefined ? connInfo.storage : existingConnInfo.storage,
        pool: connInfo.pool || existingConnInfo.pool
      };
      
      // 关闭现有连接
      if (this.connections.has(name)) {
        try {
          const connection = this.connections.get(name)!;
          await connection.close();
          console.log(`数据库 '${name}' 现有连接已关闭`);
          // 从连接映射中移除
          this.connections.delete(name);
        } catch (error: any) {
          console.error(`关闭数据库连接 '${name}' 错误:`, error.message);
        }
      }
      
      // 更新数据库中的连接信息
      await existingConn.update(updatedConnInfo);
      
      // 更新配置映射
      this.connectionConfigs.set(name, updatedConnInfo);
      console.log(`数据库 '${name}' 配置已更新`);
      
      // 如果需要重新连接
      if (reconnect) {
        return await this.connectToDatabase(updatedConnInfo);
      }
      
      return true;
    } catch (error: any) {
      console.error(`更新数据库连接失败:`, error.message);
      throw error;
    }
  }
  
  // 测试现有连接
  async testConnection(name: string): Promise<boolean> {
    try {
      // 确保数据库已连接
      let connection;
      try {
        connection = this.getConnection(name);
      } catch (error) {
        // 如果未连接，尝试连接
        connection = await this.getOrCreateConnection(name);
      }
      
      await connection.authenticate();
      console.log(`数据库 '${name}' 连接测试成功`);
      return true;
    } catch (error: any) {
      console.error(`数据库 '${name}' 连接测试失败:`, error.message);
      return false;
    }
  }
  
  // 测试新的数据库连接配置但不保存
  async testConnectionConfig(connInfo: DatabaseConnectionInfo): Promise<boolean> {
    try {
      const dbConfig: any = {
        dialect: connInfo.dialect || 'mysql',
        host: connInfo.host || 'localhost',
        logging: false,
        pool: {
          ...this.defaultPoolConfig,
          ...(connInfo.pool || {})
        }
      };

      if (connInfo.port) dbConfig.port = connInfo.port;
      if (connInfo.username) dbConfig.username = connInfo.username;
      if (connInfo.password) dbConfig.password = connInfo.password;
      if (connInfo.database) dbConfig.database = connInfo.database;
      if (connInfo.storage) dbConfig.storage = connInfo.storage;

      const sequelize = new Sequelize(dbConfig);
      await sequelize.authenticate();
      await sequelize.close();
      
      console.log(`数据库连接配置测试成功`);
      return true;
    } catch (error: any) {
      console.error(`数据库连接配置测试失败:`, error.message);
      return false;
    }
  }

  // 测试所有配置的连接
  async testAllConnections(): Promise<Map<string, boolean>> {
    const results = new Map<string, boolean>();
    for (const name of this.connectionConfigs.keys()) {
      results.set(name, await this.testConnection(name));
    }
    return results;
  }

  // 获取数据库连接 - 如果未连接则抛出错误
  getConnection(name: string): Sequelize {
    const connection = this.connections.get(name);
    if (!connection) {
      throw new Error(`数据库连接 '${name}' 未建立。请使用 getOrCreateConnection 按需建立连接。`);
    }
    return connection;
  }
  
  // 获取或创建数据库连接 - 按需连接模式
  async getOrCreateConnection(name: string): Promise<Sequelize> {
    // 如果连接已存在，直接返回
    if (this.connections.has(name)) {
      return this.connections.get(name)!;
    }
    
    // 检查是否有配置
    if (this.connectionConfigs.has(name)) {
      const connInfo = this.connectionConfigs.get(name)!;
      const success = await this.connectToDatabase(connInfo);
      if (!success) {
        throw new Error(`无法连接到数据库 '${name}'`);
      }
      return this.connections.get(name)!;
    }
    
    // 配置不存在
    throw new Error(`数据库连接配置 '${name}' 不存在。请先添加连接配置。`);
  }

  // 获取所有已连接的数据库名称
  getConnectionNames(): string[] {
    return Array.from(this.connections.keys());
  }
  
  // 获取所有配置的数据库名称
  getConfiguredConnectionNames(): string[] {
    return Array.from(this.connectionConfigs.keys());
  }

  getDataWiseDir(): string {
    return this.dataWiseDir;
  }

  // 获取连接详情
  getConnectionDetails(): Array<{
    name: string;
    dialect: string;
    host: string;
    database?: string;
    pool?: PoolConfig;
    connected: boolean;
  }> {
    const details: Array<{
      name: string;
      dialect: string;
      host: string;
      database?: string;
      pool?: PoolConfig;
      connected: boolean;
    }> = [];

    // 遍历所有配置
    for (const [name, config] of this.connectionConfigs.entries()) {
      details.push({
        name,
        dialect: String(config.dialect || 'mysql'),
        host: String(config.host || 'localhost'),
        database: config.database,
        pool: config.pool,
        connected: this.connections.has(name)
      });
    }

    return details;
  }
  
  // 获取所有数据库连接的完整信息
  async getAllConnectionsInfo(): Promise<Array<DatabaseConnectionInfo & { connected: boolean }>> {
    try {
      // 确保配置数据库已初始化
      if (!this.configInitialized) {
        await this.initConfigDb();
      }
      
      const connections = await this.ConnectionModel.findAll();
      return connections.map((conn: any) => {
        const info = conn.toJSON();
        return {
          ...info,
          connected: this.connections.has(info.name)
        };
      });
    } catch (error: any) {
      console.error('获取数据库连接信息失败:', error.message);
      throw error;
    }
  }

  // 执行查询
  async executeQuery(dbName: string, sql: string, params: any[] = []): Promise<any[]> {
    // 确保数据库已连接
    let sequelize;
    try {
      sequelize = this.getConnection(dbName);
    } catch (error) {
      console.log(`数据库 '${dbName}' 未连接，尝试按需连接...`);
      sequelize = await this.getOrCreateConnection(dbName);
    }
    
    return await sequelize.query(sql, {
      replacements: params,
      type: QueryTypes.SELECT
    });
  }

  // 获取表结构
  async describeTable(dbName: string, tableName: string): Promise<any> {
    // 确保数据库已连接
    let sequelize;
    try {
      sequelize = this.getConnection(dbName);
    } catch (error) {
      console.log(`数据库 '${dbName}' 未连接，尝试按需连接...`);
      sequelize = await this.getOrCreateConnection(dbName);
    }
    
    return await sequelize.getQueryInterface().describeTable(tableName);
  }

  // 获取所有表
  async listTables(dbName: string): Promise<string[]> {
    // 确保数据库已连接
    let sequelize;
    try {
      sequelize = this.getConnection(dbName);
    } catch (error) {
      console.log(`数据库 '${dbName}' 未连接，尝试按需连接...`);
      sequelize = await this.getOrCreateConnection(dbName);
    }
    
    return await sequelize.getQueryInterface().showAllTables();
  }

  // 关闭所有连接
  async closeAll(): Promise<void> {
    for (const [name, connection] of this.connections.entries()) {
      try {
        await connection.close();
        console.log(`数据库 '${name}' 连接已关闭`);
      } catch (error: any) {
        console.error(`关闭数据库连接 '${name}' 错误:`, error.message);
      }
    }
    
    // 关闭配置数据库
    if (this.configDB) {
      try {
        await this.configDB.close();
        console.log('配置数据库连接已关闭');
      } catch (error: any) {
        console.error('关闭配置数据库错误:', error.message);
      }
    }
    
    this.connections.clear();
  }
  
  // 断开特定数据库连接
  async disconnectDatabase(name: string): Promise<boolean> {
    try {
      if (!this.connections.has(name)) {
        console.log(`数据库 '${name}' 未连接，无需断开连接`);
        return true;
      }
      
      const connection = this.connections.get(name)!;
      await connection.close();
      
      // 从连接映射中移除
      this.connections.delete(name);
      
      console.log(`数据库 '${name}' 连接已关闭`);
      return true;
    } catch (error: any) {
      console.error(`断开数据库连接 '${name}' 失败:`, error.message);
      throw error;
    }
  }
} 